[dbo].[asi_OpportunityActionPlanUpgradeReport]
CREATE PROCEDURE dbo.asi_OpportunityActionPlanUpgradeReport AS
IF EXISTS(SELECT 1 FROM sysobjects WHERE name='ActionPlan')
EXEC
('
DECLARE @idoc int
DECLARE @doc varchar(8000)
DECLARE @planName nvarchar(50)
DECLARE @ActionPlanKey uniqueidentifier
SET NOCOUNT ON
SELECT ActionPlanKey, Name, ActionManifest
INTO #Plans
FROM ActionPlan
CREATE TABLE #Assignee
(
ActionPlanKey uniqueidentifier,
PlanName nvarchar(50),
taskId uniqueidentifier,
taskSubject nvarchar(50),
taskDescription nvarchar(100),
Id uniqueidentifier,
userKey uniqueidentifier,
assigneeType nvarchar(50),
groupTag nvarchar(50)
)
SELECT TOP 1 @doc = convert(varchar(8000), ActionManifest), @planName = [Name], @ActionPlanKey = ActionPlanKey
FROM #Plans
WHILE (@@ROWCOUNT <> 0)
BEGIN
EXEC sp_xml_preparedocument @idoc output, @doc
INSERT INTO #Assignee
SELECT ActionPlanKey = @ActionPlanKey, PlanName = @planName, *
FROM OPENXML(@idoc, ''/ActionManifest/Stage/EmailAction/Assignee'', 2)
WITH (
taskId uniqueidentifier ''../@Id'',
taskSubject nvarchar(100) ''../@taskSubject'',
taskDescription nvarchar(100) ''../@description'',
Id uniqueidentifier ''@Id'',
userKey uniqueidentifier ''@userKey'',
assigneeType nvarchar(100) ''@assigneeType'',
groupTag nvarchar(100) ''@groupTag''
)
INSERT INTO #Assignee
SELECT ActionPlanKey = @ActionPlanKey, PlanName = @planName, *
FROM OPENXML(@idoc, ''/ActionManifest/Stage/TaskAction/Assignee'', 2)
WITH (
taskId uniqueidentifier ''../@Id'',
taskSubject nvarchar(100) ''../@taskSubject'',
taskDescription nvarchar(100) ''../@description'',
Id uniqueidentifier ''@Id'',
userKey uniqueidentifier ''@userKey'',
assigneeType nvarchar(100) ''@assigneeType'',
groupTag nvarchar(100) ''@groupTag''
)
EXEC sp_xml_removedocument @idoc
DELETE FROM #Plans WHERE ActionPlanKey = @ActionPlanKey
SELECT TOP 1 @doc = convert(nvarchar(4000), ActionManifest), @planName = [Name], @ActionPlanKey = ActionPlanKey
FROM #Plans
END
SET NOCOUNT OFF
DROP TABLE #Plans
SELECT * FROM #Assignee
WHERE assigneeType <> ''Group''
DROP TABLE #Assignee')
GO
